package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.json.JSONArray;

import fy.config.FyConfig;
import fy.form.listen.set.OA_UAPAccountContrast;
import fy.uap.bean.SupplierBean;
import fy.util.DBUtil;

public class QuerySupplierInfoByCodeDao {

	private static String querySupplierVendor = "select v.* from fy_supplier_vendor v where cvencode = ? and (dr='0' or dr is null) and not exists ( select * from fy_supplier_vendor_invclass vi where vi.pk_vendor = v.pk_vendor and vi.cvendorclass = 3)";

	public static SupplierBean querySupplierInfo(String supplierCode) {
		return querySupplierInfo(supplierCode, "2");
	}

	public static SupplierBean querySupplierInfo(String supplierCode,
			long loginAccount) {
		String uapAccountID = OA_UAPAccountContrast.getUapAccountID(String
				.valueOf(loginAccount));

		if (uapAccountID == null) {
			return querySupplierInfo(supplierCode, "2");
		} else {
			Connection conn = null;
			PreparedStatement pst = null;
			ResultSet rs = null;
			SupplierBean bean = null;
			try {
				conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

				pst = conn.prepareStatement(querySupplierVendor);

				pst.setString(1, supplierCode);

				rs = pst.executeQuery();

				if (rs.next()) {
					bean = new SupplierBean();
					bean.setProviderName(rs.getString("cvenname"));
					setSupplierBean(rs, bean);

					DBUtil.close(rs, pst);

					pst = conn
							.prepareStatement("select i.pk_invclass,i.cvendorclass from fy_supplier_vendor_invclass i, fy_supplier_vendor v where i.pk_vendor = v.pk_vendor and v.cvencode = ? and (i.dr='0' or i.dr is null) and i.cvendorclass <> '3' and ( (i.denddate is null or to_date(i.denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' ))) and trim(i.pk_corp)=?");

					pst.setString(1, supplierCode);
					pst.setString(2, uapAccountID);
					rs = pst.executeQuery();

					JSONArray potentialSuppliers = new JSONArray();
					JSONArray stopSuppliers = new JSONArray();
					JSONArray qualifiedSuppliers = new JSONArray();

					while (rs.next()) {
						if ("1".equals(rs.getString(2))) {
							qualifiedSuppliers.put(rs.getString(1));
						} else if ("2".equals(rs.getString(2))) {
							potentialSuppliers.put(rs.getString(1));
						} else if ("3".equals(rs.getString(2))) {
							stopSuppliers.put(rs.getString(1));
						}
					}
					DBUtil.close(rs, pst);
					bean.setPotentialSuppliers(potentialSuppliers);
					bean.setStopSuppliers(stopSuppliers);
					bean.setQualifiedSuppliers(qualifiedSuppliers);
					potentialSuppliers = null;
					stopSuppliers = null;
					qualifiedSuppliers = null;
				}

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DBUtil.close(rs);
				DBUtil.close(pst);
				DBUtil.close(conn);
			}

			return bean;
		}
	}

	public static SupplierBean querySupplierInfo(String supplierCode,
			String type) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		SupplierBean bean = null;
		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

			pst = conn.prepareStatement(querySupplierVendor);

			pst.setString(1, supplierCode);

			rs = pst.executeQuery();

			if (rs.next()) {
				bean = new SupplierBean();
				bean.setProviderName(rs.getString("cvenname"));
				if ("2".equals(type)) {
					setSupplierBean(rs, bean);
				}
				// DBUtil.closeResultSet(rs);
				// DBUtil.closeStatement(pst);
				// pst = conn.prepareStatement("");
				// pst =
				// conn.prepareStatement("select * from fy_supplier_vendor where cvencode = ?");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}

		return bean;
	}

	// private static JSONArray queryApptitude(Connection conn, String code)
	// throws SQLException, JSONException {
	// JSONArray ja = new JSONArray();
	// PreparedStatement pst = null;
	// ResultSet rs = null;
	// try {
	// pst = conn
	// .prepareStatement("select a.pk_venapt, a.aptitudeclass, a.starttime, a.endtime, c.starttime,c.endtime,max(to_date(c.usetime,'yyyy-mm-dd')) from fy_supplier_vendor_aptitude a join fy_supplier_change_aptitude c on a.pk_venapt = c.pk_venapt and nvl(a.dr,0) = 0 and nvl(c.dr,0) = 0 left join fy_supplier_vendor v on v.pk_vendor = a.pk_vendor and nvl(v.dr,0) = 0 where v.cvencode = ? group by a.pk_venapt, a.aptitudeclass, a.starttime, a.endtime, c.starttime,c.endtime");
	// pst.setString(1, code);
	//
	// rs = pst.executeQuery();
	// while (rs.next()) {
	// JSONObject jo = new JSONObject();
	// jo.put("pk_venapt", rs.getString(1));
	// jo.put("aptitudeClass", rs.getString(2));
	// jo.put("startTime",
	// MyUtil.strIsNotNull(rs.getString(5)) ? rs.getString(5)
	// : rs.getString(3));
	// jo.put("endTime",
	// MyUtil.strIsNotNull(rs.getString(6)) ? rs.getString(6)
	// : rs.getString(4));
	// ja.put(jo);
	// jo = null;
	// }
	//
	// } finally {
	// DBUtil.close(rs, pst);
	// }
	//
	// return ja;
	// }

	private static void setSupplierBean(ResultSet rs, SupplierBean bean)
			throws SQLException {
		bean.setAddress(rs.getString("cveniaddress"));
		bean.setLinkman(rs.getString("cvenperson"));
		bean.setSupplyCycle(rs.getString("cycle"));
		bean.setCvenbank(rs.getString("cvenbank"));
		bean.setSupplyAddress(rs.getString("cvenaddress"));
		bean.setTel(rs.getString("cvenphone"));
		bean.setCpurbillcode(rs.getString("cpurbillcode"));
		bean.setBankAccount(rs.getString("cvenaccount"));
		bean.setTaxRegister(rs.getString("cvenregcode"));

		bean.setFax(rs.getString("cvenfax"));
		bean.setCouttype(rs.getString("couttype"));
		bean.setCmoneytype(rs.getString("cmoneytype"));
		bean.setQualitySystem(rs.getString("cqualitycond"));
		bean.setPostCode(rs.getString("cvenpostcode"));
		bean.setCompanyType(rs.getString("ctrade"));
		bean.setCcuspaycond(rs.getString("ccuspaycond"));
		bean.setProperty(rs.getString("dorf"));
		bean.setEmail(rs.getString("cvenemail"));
		bean.setCcontent(rs.getString("ccontent"));
		bean.setcVenBankMemo(rs.getString("cVenBankMemo"));

		bean.setPk_vendor(rs.getString("pk_vendor"));
		bean.setCvenfygl(rs.getString("cvenfygl"));

		bean.setQylb(rs.getString("enterprise_type"));
		bean.setZczj(rs.getString("registered_capital"));
		bean.setClsj(rs.getString("establish_time"));
		bean.setNxse(rs.getString("annual_sales"));
		bean.setZgrs(rs.getString("worker_number"));
		bean.setJycs(rs.getString("premises"));
		bean.setFrdb(rs.getString("cvenlperson"));
		bean.setFrdbsfz(rs.getString("legal_person_id_card"));
		// enterprise_type VARCHAR2 (100) , --企业类别
		// registered_capital VARCHAR2 (100) , --注册资金
		// establish_time VARCHAR2 (100) , --成立时间
		// annual_sales VARCHAR2 (100) , --年销售额
		// worker_number VARCHAR2 (100) , --职工总人数
		// premises VARCHAR2 (100) , --经营场所
		// legal_person_id_card VARCHAR2 (100) --法人代表身份证
		// 法人代表字段已经存在：cvenlperson VARCHAR2 (20)

	}

	public static String checkProviderCodeIsExist(String code) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String returnMsg = null;
		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

			pst = conn
					.prepareStatement("select 1,v.cvenname from fy_supplier_vendor v where cvencode = ? and (dr='0' or dr is null) union all select 2,a.vendor_name from APPS.AP_VENDORS_V@"
							+ FyConfig.getERP_DB_CONFIG().getDblink()
							+ " a where a.vendor_number = ?");

			pst.setString(1, code);
			pst.setString(2, code);

			rs = pst.executeQuery();

			if (rs.next()) {
				if ("2".equals(rs.getString(1))) {
					returnMsg = "注意：ERP系统中已存在供应商“" + rs.getString(2) + "("
							+ code + ")”";
				} else
					returnMsg = "2";
			} else {
				returnMsg = "1";
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}

		return returnMsg;
	}

	// public static void main(String[] args) {
	// Connection conn = null;
	// PreparedStatement pst = null;
	// ResultSet rs = null;
	// SupplierBean bean = null;
	// try {
	// conn = DBUtil.connect("oracle.jdbc.driver.OracleDriver",
	// "jdbc:oracle:oci:@192.168.254.55:1521:orcl", "lsj",
	// "123456");
	//
	// pst = conn
	// .prepareStatement("select * from fy_supplier_vendor where cvencode = ?");
	//
	// pst.setString(1, "123456789");
	//
	// rs = pst.executeQuery();
	//
	// if (rs.next()) {
	// bean = new SupplierBean();
	// bean.setProviderName(rs.getString("cvenname"));
	// bean.setAddress(rs.getString("cveniaddress"));
	// bean.setLinkman(rs.getString("cvenperson"));
	// bean.setSupplyCycle(rs.getString("cycle"));
	// bean.setCvenbank(rs.getString("cvenbank"));
	// bean.setSupplyAddress(rs.getString("cvenaddress"));
	// bean.setTel(rs.getString("cvenphone"));
	// bean.setCpurbillcode(rs.getString("cpurbillcode"));
	// bean.setBankAccount(rs.getString("cvenaccount"));
	// bean.setTaxRegister(rs.getString("cvenregcode"));
	//
	// bean.setFax(rs.getString("cvenfax"));
	// bean.setCouttype(rs.getString("couttype"));
	// bean.setCmoneytype(rs.getString("cmoneytype"));
	// bean.setQualitySystem(rs.getString("cqualitycond"));
	// bean.setPostCode(rs.getString("cvenpostcode"));
	// bean.setCompanyType(rs.getString("ctrade"));
	// bean.setCcuspaycond(rs.getString("ccuspaycond"));
	// bean.setProperty(rs.getString("dorf"));
	// bean.setEmail(rs.getString("cvenemail"));
	// bean.setCcontent(rs.getString("ccontent"));
	//
	// bean.setPk_vendor(rs.getString("pk_vendor"));
	//
	// // DBUtil.closeResultSet(rs);
	// // DBUtil.closeStatement(pst);
	// // pst = conn.prepareStatement("");
	//
	// }
	//
	// } catch (Exception e) {
	// e.printStackTrace();
	// } finally {
	// DBUtil.close(rs);
	// DBUtil.close(pst);
	// DBUtil.close(conn);
	// }
	//
	// System.out.println(bean.toJSON());
	// }
}
